Some business intelligence (BI) systems retrieve and process data from remote data sources, storing the data in local storage. User queries are answered by retrieving the cached data from the local storage. In some BI systems, a query processing system receives a user query and translates the query into a set of remote SQL queries. The user query may be in the form of a text string based on a query language. The set of remote SQL queries may be sent to one or more remote SQL databases. Upon receiving the result sets from the one or more remote SQL databases, the query processing system may perform post-processing to assemble an answer to the original user query. Examples of post-processing may be combination operations or result set manipulation operations. A combination operation is one that combines two or more result sets. Examples of combination operations are joins, or unions. A manipulation operation is one that changes the ordering or grouping of a result set or limits the number of rows or data elements in the result set. Examples of manipulations operations are aggregations, orderby, groupby, filters, or the like.
Some user queries may be translated into multiple queries that are sent to a single remote SQL database. The multiple result sets are then post-processed to create a single set of results as an answer to the original query.
A query processing system may employ a data model, created by a modeler. A data model, or simply a model, may include metadata that specifies the available fields, field properties, measures, and definitions of measures. The term “fields” refers to columns in tables. The data model may specify the data sources corresponding to each table. A model may also specify relationships between tables. A client system may retrieve metadata from the model to facilitate setting up a user interface to a user. The user interface may guide the user as to the available data, and enables the user to select combinations of available fields and measures. The user does not need to know how the data is retrieved, combined, or otherwise processed. Though a model facilitates querying by a user, it typically limits queries to those enabled by the model itself, though the source databases are not so restrictive.